<?php

/**
 * @property StudentUser $StudentUser Description
 * @property BankUser $BankUser Description
 * @property Transaction $Transaction Description
 * @property Config $Config Description
 * @property TransactionBankDetail $TransactionBankDetail Description
 */
class BankController extends AppController
{
    # Controller Name to be used

    var $name = 'Bank';


    # Models to be used
    var $uses = array(
        'BankUser', 'StudentUser', 'BankBranch', 'Transaction', 'Config', 'TransactionBankDetail', 'StudentNotification',
        'BankReportTracking', 'FeeType', 'BankReportTracking', 'BankReportLog', 'BankFailedLogin','BankCreditAuthorization',
        'BankReversalAuthorization'
    );

    # set the layout to use
    var $layout = 'bank_layout';

    function __validateLoginStatus()
    {
        //debug($this->Session->read('loginData'));
        if ($this->params['action'] != 'login' && $this->params['action'] != 'logout') {
            if ($this->Session->check('loginData') == false) {
                $this->redirect(array('action' => 'login?ref=' . $this->params['action']));
            } else {
                $auth_user = $this->Session->read('loginData');

                //check if the user has not done multiple login
                $db_login_session = $this->getLoginSession($auth_user['id']);
                $login_session = $auth_user['login_session'];

                if ($db_login_session != $login_session) {
                    $this->Session->setFlash('Your session has expired!');
                    $this->redirect(array('action' => 'logout'));
                }

                //authorize the user, if the user is not authorized to view this page then redirect them to their session
                if ($auth_user['user_type'] != 'bank_user') {
                    if ($auth_user['user_type'] == 'staff_user') {
                        $this->redirect(array('controller' => 'Staff', 'action' => 'index'));
                    } elseif ($auth_user['user_type'] == 'student_user') {
                        $this->redirect(array('controller' => 'Student', 'action' => 'index'));
                    } else {
                        $this->Session->setFlash('Unauthorised Access');
                        $this->redirect(array('action' => 'logout'));
                    }
                } else { // if bank_user, determine level and redirect to appropriate home screen
                    $action = $this->params['action'];
                    $user_level = $auth_user['user_level'];
                    if ($user_level == 'Admin') {
                        $res = is_int(strpos($action, 'admin'));
                        if (!$res) {
                            $this->redirect(array('action' => 'admin_users'));
                        }
                    } elseif ($user_level == 'Normal') {
                        $res = is_int(strpos($action, 'admin'));
                        if ($res) {
                            $this->redirect(array('action' => 'index'));
                        }
                    } elseif ($user_level == 'Supervisor') {
                        $res = is_int(strpos($action, 'admin'));
                        if ($res) {
                            $this->redirect(array('action' => 'supervisor'));
                        }
                    }
                }
            }
        }
    }

    private function getUserBankDetails()
    {
        $auth_user = $this->Session->read('loginData');
        $data = $this->BankBranch->find('first', array(
            'conditions' => array('BankBranch.id' => $auth_user['bank_branch_id']),
            'contain'=>array('Bank'),
            'recursive' => 1
        ));
        $data['User']['id'] = $auth_user['id'];
        $data['User']['name'] = $auth_user['fname'].' '.$auth_user['lname'];
        //debug($data);
        return $data;
    }

    private function getLoginSession($user_id)
    {
        $data = $this->BankUser->find('first', array(
            'conditions' => array('id' => $user_id),
            'recursive' => -1
        ));
        return $data['BankUser']['login_session'];
    }

    function beforeFilter()
    {
        parent::beforeFilter();
        $this->__validateLoginStatus();
    }

    public function login()
    {
        if ($this->request->is('post')) {
            $logme  = $this->request->data['BankUser'];
            $logme = $this->BankUser->sanitize($logme);
            $result = $this->BankUser->login($logme['username'], $logme['password']);
            if ($result['status']) {
                //$log_count = $this->BankFailedLogin->getLoginFailedAttempts($this->request->data['BankUser']['username'], $_SERVER['REMOTE_ADDR']);
                // $log_count = $this->BankFailedLogin->getLoginFailedAttempts($this->request->data['BankUser']['username']);
                //debug($log_count);
                /*if($log_count >= 3){
                    $this->BankFailedLogin->disableByUsername($this->request->data['BankUser']['username']);
                    $this->Session->setFlash('Your Account has been disabled!');
                }*/
                //else{
                //start a new session for this user
                $loginData = $this->createLoginSession($result['data']['BankUser']);
                //write data to session
                $this->Session->write('loginData', $loginData);
                $this->redirect(array('action' => 'index'));
                // }
            } else {
                //add to login attempt
                if ($result['log-failed-attempt']) {
                    $this->BankFailedLogin->addFailedLogin($logme['username'], $_SERVER['REMOTE_ADDR']);
                }
                $this->Session->setFlash($result['msg']);
            }
        }
        //debug(date('Y-m-d H:i:s'));
        $this->layout = 'login_layout';
    }

    private function createLoginSession($data)
    {
        $login_session = $this->randomString(50);
        $data['login_session'] = $login_session;
        $this->BankUser->save(array(
            'id' => $data['id'],
            'login_session' => $login_session
        ));
        return $data;
    }

    public function logout()
    {
        $this->Session->delete('loginData');
        $this->Session->destroy();
        $this->redirect(array('action' => 'login'));
    }

    # Controller actions
    /**
     * @name index
     * @param void
     * @return Array of data.
     * @access public
     */

    function index()
    {
        /* $stu_data  = $this->StudentUser->find('all',array(
             'fields'=>array('id','cellphone'),
             'recursive'=>-1
         ));
         //debug(count($stu_data));
         foreach($stu_data as $value){
             $st = $value['StudentUser'];
             $send_no = $st['cellphone'];
             $fon_number = '';
             $cell = '';
             if(strpos($send_no,',') !== false){
                 $num_arr = explode(',',$send_no);
                 $fon_number = trim($num_arr[0]);
             }
             elseif(strpos($send_no,'/') !== false){
                 $num_arr = explode('/',$send_no);
                 $fon_number = trim($num_arr[0]);
             }
             else{
                 $fon_number = trim($send_no);
             }
             //remove the dash separator if any
             $fon_number = preg_replace('/-/', '', $fon_number);
             if(strlen($fon_number) < 10){//not a mobile number
                // return false;
             }
             $country_code = substr($fon_number, 0, 3);
             $cell = $fon_number;
             if($country_code != '233'){
                 $cell = '233'.substr($fon_number, 1);
             }

             //$cd = date("Y-m-d", strtotime($st['date_of_birth']));
             //$new_pwd = md5($cd);
             $this->StudentUser->save(array(
                 'id'=>$st['id'],
                 'cellphone'=>$cell
             ));
         }
         debug(count($stu_data));*/
        //debug($stu_data);

        //$this->set(compact('page_title'));
    }

    function tellers($type = 'get')
    {
        $auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] == 'Supervisor' || $auth_user['user_level'] == 'Control User') { //For Supervisor and Control User
        } else {
            $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
        }

        if ($this->request->is('ajax')) {
            $this->autoRender = false;
            $this->autoLayout = false;
            $auth_user = $this->Session->read('loginData');
            switch ($type) {
                case 'get' :

                    $_POST = $this->BankUser->sanitize($_POST);

                    /**  Get posted data */
                    $page = isset($_POST['page']) ? $_POST['page'] : 1;
                    /** The current page */
                    $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
                    /** Sort column */
                    $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
                    /** Sort order */
                    $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
                    /** Search column */
                    $search_query = isset($_POST['query']) ? $_POST['query'] : '';
                    /** Search string */
                    $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
                    $limit = $rp;
                    $start = ($page - 1) * $rp;

                    //get users id for this company only

                    $condition_array = array(
                        'BankUser.deleted' => 'n'
                    );
                    if ($auth_user['user_level'] == 'Supervisor') { //For Supervisor
                        $condition_array['BankUser.bank_branch_id'] = $auth_user['bank_branch_id'];
                    }


                    if (!empty($search_query)) {
                        if ($qtype == 'username') {
                            $condition_array = array(
                                'BankUser.username' => $search_query,
                                'BankUser.deleted' => 'n'
                            );
                        } else {
                            $condition_array = array(
                                "BankUser.$qtype LIKE" => $search_query . '%',
                                'BankUser.deleted' => 'n'
                            );
                        }
                    }
                    $contain = array('BankBranch');
                    $data_table = $this->BankUser->find('all', array('conditions' => $condition_array, 'contain' => $contain, 'order' => "BankUser.$sortname $sortorder", 'limit' => $start . ',' . $limit, 'recursive' => 1));
                    $data_table_count = $this->BankUser->find('count', array('conditions' => $condition_array, 'recursive' => -1));

                    $total_records = $data_table_count;

                    if ($data_table) {
                        $return_arr = array();
                        foreach ($data_table as $obj) {
                            $return_arr[] = array(
                                'id' => $obj['BankUser']['id'],
                                'cell' => array(
                                    $obj['BankUser']['id'],
                                    $obj['BankUser']['fname'],
                                    $obj['BankUser']['mname'],
                                    $obj['BankUser']['lname'],
                                    $obj['BankUser']['username'],
                                    $obj['BankUser']['user_level'],
                                    $obj['BankUser']['active'],
                                    $obj['BankBranch']['branch_name'],
                                ),
                                'property' => array(
                                    'bg_color' => $obj['BankUser']['bg_color']
                                )
                            );
                        }
                        return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
                    } else {
                        return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
                    }
                    break;

                case 'save' :
                    if ($_POST['id'] == 0) {
                        //check if username does not exist for in this company
                        $username = $_POST['username'];
                        $res = $this->_validateUsername($username);
                        if ($res) {
                            return json_encode(array('success' => 1, 'msg' => 'Username already exist.'));
                        }
                    }
                    $data = array('BankUser' => $_POST);
                    $data['BankUser']['user_type'] = 'bank_user';
                    $pass = '';
                    if ($_POST['id'] == 0) {
                        $pass = $this->randomPassString(8);
                        $data['BankUser']['password'] = md5($pass);
                    } else {
                        $bk_user = $this->BankUser->getUserById($_POST['id']);
                        if ($bk_user['BankUser']['active'] == 'Disabled' && $_POST['active'] == 'Enabled') {
                            $data['BankUser']['bg_color'] = '';
                            $modObj = ClassRegistry::init('BankFailedLogin');
                            $re = $modObj->deleteAll(array('BankFailedLogin.username' => $_POST['username']), false);
                        }
                    }

                    if ($_POST['id'] > 0) {
                        $data['BankUser']['modified_by'] = $auth_user['id'];
                    } else {
                        $data['BankUser']['created_by'] = $auth_user['id'];
                    }

                    if ($this->BankUser->save($data['BankUser'])) {
                        if ($_POST['id'] > 0) {
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved'));
                        } else { //If new pass back extra data if needed.
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved!  The default password is ' . $pass, 'id' => $this->BankUser->id));
                        }
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Some errors occurred.'));
                    }
                    break;

                case 'reset_password':
                    $data = array(
                        'id' => $_POST['id'],
                        'password' => md5(trim($_POST['password']))
                    );
                    if ($this->BankUser->save($data)) {
                        return json_encode(array('code' => 0, 'msg' => 'Password has been reset.'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Password could not be reset.'));
                    }

                    break;

                case 'delete':
                    $ids = $_POST['ids'];
                    $modObj = ClassRegistry::init('BankUser');
                    $result = $modObj->updateAll(
                        array('BankUser.deleted' => "'y'"),
                        array('BankUser.id' => $ids)
                    );
                    if ($result) {
                        echo json_encode(array('code' => 0, 'msg' => 'Data Deleted!'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Data cannot be deleted'));
                    }
                    break;
            }
        }

        $condition_array = array();
        if ($auth_user['user_level'] == 'Supervisor') { //For Supervisor
            $condition_array['BankBranch.id'] = $auth_user['bank_branch_id'];
        }
        $list = $this->BankBranch->find('all', array(
            'conditions' => $condition_array,
            'recursive' => -1
        ));
        $branch_list = array();
        foreach ($list as $value) {
            $branch_list[] = array('id' => $value['BankBranch']['id'], 'name' => $value['BankBranch']['branch_name']);
        }

        //$this->layout = 'bank_admin_layout';

        $this->set(compact('branch_list'));
    }


    function editprofile()
    {
        if ($this->request->is('post')) {
            $error = false;
            $auth_user = $this->Session->read('loginData');

            //Check if the student is trying to use the same pass as the date of birth , then reject
            /*if(trim($this->request->data['BankUser']['password']) == $auth_user['date_of_birth']){
                $error = true;
                $this->Session->setFlash("Password Error, You can't use your default password as a new password. ");
            }*/

            if ($this->request->data['BankUser']['password']) {
                //echo 'yes';
                $this->request->data['BankUser']['password'] = md5(trim($this->request->data['BankUser']['password']));
                /* if($auth_user['first_time_pass_changed'] == 'No'){
                     $this->request->data['BankUser']['first_time_pass_changed'] = 'Yes';
                 }*/
            } else {
                $auth_user = $this->Session->read('loginData');
                $this->request->data['BankUser']['password'] = $auth_user['password'];
            }
            /* if($this->request->data['StudentUser']['cellphone']){
                 $cell = $this->request->data['StudentUser']['cellphone'];
                 $this->request->data['StudentUser']['cellphone'] = preg_replace('/-/', '', $cell);
             }*/

            if ($error) {
                $this->Session->write('process_error', 'yes');
            } else {
                $result = $this->BankUser->save($this->request->data);

                if ($result) {
                    //write data to session
                    $user = $this->BankUser->getUserById($this->request->data['BankUser']['id']);
                    $this->Session->write('loginData', $user['BankUser']);
                    $this->Session->setFlash('Your profile info has been updated !');
                    $this->Session->write('process_error', 'no');
                    $msg = "Dear " . $auth_user['fname'] . " your account profile has been updated.";
                } else {
                    $this->Session->setFlash('Sorry, your profile info could not be updated');
                    $this->Session->write('process_error', 'yes');
                }
            }

            $this->redirect(array('action' => 'editprofile'));
        }
        $controller = $this;
        $this->set(compact('controller'));
    }


    public function search()
    {
        if (isset($this->request->data) && isset($this->request->data['Student'])) {
            $reg_no = $this->request->data['Student']['id'];

            $student = $this->StudentUser->find('first', array(
                    'contain' => array(
                        'Program',
                        'Transaction' => array(
                            'conditions' => array(
                                'trans_type' => 'credit_account',
                                'reverted' => 0
                            ),
                            'order' => array('id' => 'desc'),
                            'limit' => 5,
                            "TransactionBankDetail",
                            "TransactionItem",
                            "BankUser"
                        )
                    ),
                    'conditions' => array(
                        'OR' => array( /* 'StudentUser.reg_no' => $reg_no, */
                            'StudentUser.index_number' => $reg_no)
                    ))
            );

            if ($student) {
                $bank_info = $this->getUserBankDetails();
                $this->set('student', $student);
                $this->set('search_term', $reg_no);
                $this->set('bank_info', $bank_info);
            } else {
                $this->Session->setFlash('No Student Found With ID or Last Name supplied');
                $this->redirect(array('action' => 'index'));
            }
        } else {
            $this->redirect(array('action' => 'index'));
        }
    }

    /**
     * Credit a Student's Account with a Cash or Cheque Payment
     */
    public function credit_account()
    {
        $this->autoRender = false;

        if ($this->request->data['StudentUser']) {
            $data = $this->request->data;
            $amount_tendered = doubleval($data['Payee']['amount']);

            $auth_user = $this->Session->read('loginData');

            $student_info_data = $this->StudentUser->getUserById($data['StudentUser']['id']);
            $student_info = $student_info_data['StudentUser'];
            $bank_info = $this->getUserBankDetails();
            $credit_limit = doubleval($bank_info['Bank']['teller_credit_limit']);


            $trans = array(
                'Transaction' => array(
                    'trans_type' => 'credit_account',
                    'student_user_id' => $data['StudentUser']['id'],
                    'academic_session_id' => $this->Config->get('academic_session_id'),
                    'ip_address' => $_SERVER['REMOTE_ADDR'],
                    'trans_ref_no' => "CRA" . $this->Transaction->getNextAutoIncrement() . "001",
                    'bank_user_id' => $auth_user['id'],
                    'created_by' => $auth_user['id'],
                    'created_by' => $auth_user['id']
                ),
                'TransactionBankDetail' => array(
                    array(
                        'bank_branch_id' => $auth_user['bank_branch_id'],
                        'teller_id' => $auth_user['id'],
                        'payee_name' => $data['Payee']['name'],
                        'payee_phone' => $data['Payee']['phone'],
                        'payee_address' => $data['Payee']['address'],
                        'credit_account' => $this->Config->get('bank_acc_no'),
                        'credit_amt' => $data['Payee']['amount'],
                        'debit_account' => null,
                        'payment_method' => $data['Payee']['method'],
                        'payin_cheque_number' => $data['Payee']['cheque_number'],
                        'payin_bank' => $data['Payee']['cheque_bank'],
                        'created_by' => $auth_user['id']
                    )
                ),
                'TransactionItem' => array(
                    array(
                        'fee_id' => null,
                        'trans_currency' => 'GHS',
                        'credit_amt' => $data['Payee']['amount'],
                        'desc' => 'Credit Account'
                    )
                )
            );

            if($amount_tendered >= $credit_limit){//Send for Authorization,
                $trans['BankCreditAuthorization'] = array(
                    'bank_id'=>$bank_info['Bank']['id'],
                    'bank_user_id'=>$bank_info['User']['id'],
                    'bank_branch_id'=>$bank_info['BankBranch']['id'],
                    'authorize_amount'=>$amount_tendered,
                );
                $trans['Transaction']['authorize'] = 'Pending';
            }

            $saved = $this->Transaction->saveAll($trans);

            if ($saved) {
                if($amount_tendered >= $credit_limit){//Send for Authorization,
                    $msg = "Dear " . $student_info['fname'] . " an amt of GHS " . doubleval($data['Payee']['amount']) . " will be credited to your UPSA wallet in an hours time from now. If your wallet is not credited by then, please contact Access Bank. Visit http://interpayafrica.com/student to check your balance and complete payment. ";
                    $notify_msg = "Your UPSA account will be credited with <b> GHC " . doubleval($data['Payee']['amount']) . "</b> in an hours time from now. If your wallet is not credited by then, please contact Access Bank";
                }
                else{
                    // update student account balance
                    $this->StudentUser->creditAccount($data['StudentUser']['id'], doubleval($data['Payee']['amount']));
                    $bal = doubleval($student_info['account_balance']) + doubleval($data['Payee']['amount']);
                    $msg = "Dear " . $student_info['fname'] . " an amt of GHS " . doubleval($data['Payee']['amount']) . " has been credited to your UPSA wallet. Your bal. is GHS " . $bal . ". Visit http://interpayafrica.com/student to complete payment. ";
                    $notify_msg = "Your UPSA account was credited with <b> GHC " . doubleval($data['Payee']['amount']) . "</b> on <b>" . date('D M d, Y') . "</b> at <b>" . date('g:ia') . "</b>";
                }

                //send system notifications
                $this->StudentNotification->save(array(
                    'StudentNotification' => array(
                        'student_user_id' => $data['StudentUser']['id'],
                        'notify_title' => 'Account Update Notification',
                        'notify_msg' => $notify_msg
                    )
                ));

                $fon_number = $student_info['cellphone'];
                $this->Message->sendSMS($msg, $fon_number);

                $this->redirect(array('action' => 'credit_history', $data['StudentUser']['id']));
            } else {
                echo json_encode(array('status' => 'error', 'message' => 'Student Data Not Saved Due To Server Side Error.'));
            }
        }
    }


   function credit_authorization(){
       //$this->layout = 'bank_report_layout';
       $auth_user = $this->Session->read('loginData');
       $branch_id = $auth_user['bank_branch_id'];
       if ($auth_user['user_level'] == 'Supervisor' || $auth_user['user_level'] == 'Control User') { //For Supervisor and Control User
       } else {
           $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
       }

       $conditions = array('BankCreditAuthorization.status'=>'Not Authorized','BankCreditAuthorization.reverted'=> 'Not Reverted');
       if($auth_user['user_level'] == 'Supervisor'){
           $conditions['BankCreditAuthorization.bank_branch_id']=$branch_id;
       }
       
       $this->paginate = array(
           'BankCreditAuthorization' => array(
               'fields'=>array('BankCreditAuthorization.id','BankCreditAuthorization.transaction_id','BankCreditAuthorization.bank_user_id','BankCreditAuthorization.authorize_amount','BankCreditAuthorization.status','BankCreditAuthorization.reverted','BankCreditAuthorization.modified','BankCreditAuthorization.created'),
               'conditions'=>$conditions,
               'contain' => array(
                   'Transaction'=>array(
                       'fields'=>array('Transaction.id','Transaction.student_user_id','Transaction.bank_user_id'),
                       'StudentUser'=>array('fields'=>array('StudentUser.id','StudentUser.index_number ','StudentUser.fname','StudentUser.lname','StudentUser.mname')),
                       'TransactionBankDetail'=>array('fields'=>array('TransactionBankDetail.id','TransactionBankDetail.payment_method '))
                   ),
                   'BankUser'=>array(
                       'fields'=>array('BankUser.id','BankUser.fname','BankUser.lname','BankUser.mname','BankUser.username'),
                       'BankBranch'=>array(
                           'fields'=>array('BankBranch.id','BankBranch.branch_name_full')
                       )
                   )
               ),
               'order' => array('BankCreditAuthorization.id' => 'desc'),
               'limit' => 15
           )
       );

       $transactData = $this->paginate('BankCreditAuthorization');
       $this->set('title_for_layout', 'Credit Authorization');
       $this->set(compact('page_title', 'transactData'));
   }


    function authorize($auth_id){
        //$this->layout = 'bank_report_layout';
        $auth_user = $this->Session->read('loginData');
        $branch_id = $auth_user['bank_branch_id'];
        if ($auth_user['user_level'] == 'Supervisor' || $auth_user['user_level'] == 'Control User') { //For Supervisor and Control User
        } else {
            $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
        }
        $auth_data = $this->BankCreditAuthorization->find('first',array(
            'conditions'=>array('BankCreditAuthorization.id'=>$auth_id),
            'contain' => array(
                'Transaction'=>array(
                    'StudentUser'
                )
            )
        ));

        $status = $auth_data['BankCreditAuthorization']['status'];
        if($status == 'Authorized'){
            $this->redirect(array('controller' => 'Bank', 'action' => 'credit_authorization'));
        }

        $credit_amount = doubleval($auth_data['BankCreditAuthorization']['authorize_amount']);
        $student_id = $auth_data['Transaction']['StudentUser']['id'];
        $student_old_bal = doubleval($auth_data['Transaction']['StudentUser']['account_balance']);
        $student_fname = $auth_data['Transaction']['StudentUser']['fname'];
        $student_info = $auth_data['Transaction']['StudentUser'];
       //Credit Student Wallet
        $this->StudentUser->creditAccount($student_id,$credit_amount);
        $bal = $student_old_bal + $credit_amount;
        $msg = "Dear " . $student_fname . " an amt of GHS " . $credit_amount . " has been credited to your UPSA wallet. Your bal. is GHS " . $bal . ". Visit http://interpayafrica.com/student to complete payment. ";
        $notify_msg = "Your UPSA account was credited with <b> GHC " . $credit_amount . "</b> on <b>" . date('D M d, Y') . "</b> at <b>" . date('g:ia') . "</b>";

        //send system notifications
        $this->StudentNotification->save(array(
            'StudentNotification' => array(
                'student_user_id' => $student_id,
                'notify_title' => 'Account Update Notification',
                'notify_msg' => $notify_msg
            )
        ));

        $fon_number = $student_info['cellphone'];
        $this->Message->sendSMS($msg, $fon_number);

        //Change status to authorize
        $s= array(
            'Transaction' => array(
                'id' => $auth_data['Transaction']['id'],
                'authorize' => 'Authorized',
                'modified_by' => $auth_user['id']
            ),
            'BankCreditAuthorization' => array(
                'id' => $auth_id,
                'status' => 'Authorized',
                'modified_by' => $auth_user['id']
            )
        );
        $this->Transaction->saveAll($s);
        $this->redirect(array('controller' => 'Bank', 'action' => 'credit_authorization'));
    }


    public function credit_history($student_user_id)
    {
        $student = $this->StudentUser->find('first', array(
                'contain' => array(
                    'Program',
                    'Transaction' => array(
                        'conditions' => array(
                            'trans_type' => 'credit_account',
                            'reverted' => 0
                        ),
                        'order' => array('id' => 'desc'),
                        'limit' => 5,
                        "TransactionBankDetail",
                        "TransactionItem",
                        "BankUser"

                    )
                ),
                'conditions' => array('StudentUser.id' => $student_user_id)
            )
        );

        $this->set(compact('student'));
    }


    function report_reversal()
    {
        $auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] == 'Supervisor' || $auth_user['user_level'] == 'Control User') { //For Supervisor and Control User
        } else {
            $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
        }

        $start_dt = $default_start_dt = date('Y-m-01');
        $end_dt = $default_end_dt = date('Y-m-t');
        $branch_id = $auth_user['bank_branch_id'];
        $teller = null;
        if ($this->request->is('post')) {
            $p_data = $this->request['data']['Query'];
            $p_data = $this->BankReportLog->sanitize($p_data);
            $start_dt = $default_start_dt = $p_data['start_dt'];
            $end_dt = $default_end_dt = $p_data['end_dt'];
            $branch_id = $p_data['branch'];
            $teller = $p_data['teller'];
        }
        if ($teller == 0) {
            $teller = null;
        }
        if ($branch_id == 0) {
            $branch_id = null;
        }

        $type = "'credit_reversal'";

        $data_table = $this->getTransactions($start_dt, $end_dt,$type, $branch_id, $teller);

        if ($auth_user['user_level'] == 'Supervisor') { //For Supervisor
            $data = $this->BankBranch->getBranchAndTellers($auth_user['bank_branch_id']);
            $branch_list = $data['branch_list'];
            $branch_tellers = $data['branch_tellers'];
        }
        if ($auth_user['user_level'] == 'Control User') { //Control User
            $data = $this->BankBranch->getBranchAndTellers();
            $branch_list = array_merge(array('0' => 'All Branches'), $data['branch_list']);
            $branch_tellers = $data['branch_tellers'];
        }
        $controller = $this;
        if ($teller == null) {
            $teller = 0;
        }
        if ($branch_id == null) {
            $branch_id = 0;
        }
        //debug($teller);

        $this->set(compact('page_title', 'branch_list', 'default_start_dt', 'default_end_dt', 'data_table', 'controller', 'branch_tellers', 'branch_id', 'teller'));
    }


    function credit_reversal(){
        $this->autoRender = false;
        $auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] == 'Supervisor' || $auth_user['user_level'] == 'Control User') { //For Supervisor and Control User
        }
        else {
            return json_encode(array('status' => 'failed', 'message' => "You Don't have Permission to  Revert Transaction."));
        }

        if ($this->request->data['Transaction']) {
            $transaction = $this->request->data['Transaction'];

            $r = $this->_revert($transaction['id'],$transaction['debit_amt'],$transaction['revert_reason']);
            return json_encode($r);
        }
    }


    public function revert_transaction()
    {
        $this->autoRender = false;

        if ($this->request->data['Transaction']) {
            $transaction = $this->request->data['Transaction'];

            $r = $this->_revert($transaction['id'],$transaction['debit_amt'],$transaction['revert_reason']);
            if($r['status'] == 'success'){
                $this->redirect(array('action' => 'credit_history', $transaction['student_user_id']));
            }
            else{
                echo json_encode($r);
            }
        }
    }


    public function _revert($transaction_id,$debit_amt,$reason='')
    {
        $user = $this->Session->read('loginData');
        $trans = $this->Transaction->find('first', array(
            'conditions' => array(
                'Transaction.id' => $transaction_id,
                'Transaction.trans_type' => 'credit_account'
            ),
            'contain'=>array(
                'StudentUser','BankCreditAuthorization'
            )
        ));
        $stu = $trans['StudentUser'];

        if (!$trans) {
            return array('status' => 'failed', 'message' => 'Cannot Revert Transaction. This transactions does not exit');
        }
        else {
            //if it required credit authorization
            $debit = true;
            $credit_authorized = false;
            $is_authorized = false;
            if(!empty($trans['BankCreditAuthorization']['id'])){
                $credit_authorized = true;
            }
            if($credit_authorized){
                if($trans['BankCreditAuthorization']['status'] == 'Authorized'){
                    $is_authorized = true;
                }
                else{
                    $debit = false;
                }
            }
            //debug($debit);

            //exit;
            //Reverse the transaction
            $transaction  = array(
                'Transaction'=>array(
                    'id' => $transaction_id,
                    'trans_type'=>'credit_reversal',
                    'revert_dt'=>date('Y-m-d H:i:s'),
                    'reverted'=>1,
                    'reverted_by' => $user['id'],
                    'reversal_id' => $transaction_id,
                    'revert_reason'=>$reason,
                    'modified_by' => $user['id']
                )
            );
            if($credit_authorized){
                $transaction['BankCreditAuthorization']=array(
                    'id' => $trans['BankCreditAuthorization']['id'],
                    'reverted'=>'Reverted',
                    'modified_by' => $user['id']
                );
            }

            $result = $this->Transaction->saveAll($transaction);
            if($result){
                if($debit){
                    $this->StudentUser->debitAccount($trans['StudentUser']['id'], doubleval($debit_amt));
                }
            }

            $bal = doubleval($stu['account_balance']) - doubleval($debit_amt);
            $msg = "Dear " . $stu['fname'] . " an amt of GHS " . $debit_amt . " has been debited from your UPSA wallet.. Your bal. is GHS " . $bal . "";
            $notify_msg = "Your UPSA account was debited with <b> GHC " . $debit_amt . "</b> on <b>" . date('D M d, Y') . "</b> at <b>" . date('g:ia') . "</b>";

            //send system notifications
            $this->StudentNotification->save(array(
                'StudentNotification' => array(
                    'student_user_id' => $stu['id'],
                    'notify_title' => 'Account Update Notification',
                    'notify_msg' => $notify_msg
                )
            ));

            $fon_number = $stu['cellphone'];
            $this->Message->sendSMS($msg, $fon_number);

            if($result){
                return array('status' => 'success', 'message' => 'Transaction Reverted!');
            }
            else{
                return array('status' => 'failed', 'message' => 'Cannot Revert Transaction.');
            }
        }
    }



    public function update_profile($field, $student_id)
    {

        $this->autoRender = false;

        if (isset($this->request->data['field'])) {

            $result = $this->StudentUser->save(array(
                'id' => $student_id,
                $field => $this->request->data['field']
            ));

            echo json_encode($result);
        }
    }

    public function supervisor()
    {
        //$this->layout = 'bank_supervisor_layout';

        $notifications = $this->getNotification();

        $revert_requests = $this->Transaction->find('all', array(
            'contain' => array('BankUser', 'StudentUser', 'TransactionBankDetail'),
            'conditions' => array(
                'NOT' => array('Transaction.reverted_by' => null),
                'Transaction.reverted' => 0 // still pending approval
            )
        ));

        $this->set('title_for_layout', 'Dashboard');
        $this->set(compact('page_title', 'notifications'));
        $this->set('revert_requests', $revert_requests);
    }


    public function reports()
    {
        $auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] == 'Supervisor' || $auth_user['user_level'] == 'Control User') { //For Supervisor and Control User
        } else {
            $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
        }
        $last_report_record = $this->BankReportTracking->find('first');
        $start_report_id = 0;
        if ($last_report_record) {
            $start_report_id = $last_report_record['BankReportTracking']['last_trans_id'];
        }
        $count = $this->Transaction->find('count', array('conditions' => array('id >' => $start_report_id, 'trans_type' => 'fee_payment'), 'recursive' => -1));

        $this->set(compact('title_for_layout', 'Dashboard', 'page_title', 'notifications', 'count'));
    }


    public function get_daily_report()
    {
        //$this->autoRender = false;
        $_POST = $this->BankReportLog->sanitize($_POST);
        $maker_id = $_POST['maker_id'];
        $checker_id = $_POST['checker_id'];
        $maker_time_stamp = date('Y-m-d H:i:s');
        $batch_no = $this->BankReportLog->getNewBatchNo();

        $last_report_record = $this->BankReportTracking->find('first');
        $start_report_id = 0;
        if ($last_report_record) {
            $start_report_id = $last_report_record['BankReportTracking']['last_trans_id'];
        }

        $trans_data = $this->__get_report_data('new', $start_report_id);
        foreach ($trans_data as $key => $value) {
            $trans_data[$key]['Transaction']['maker_id'] = $maker_id;
            $trans_data[$key]['Transaction']['user_id'] = $maker_id;
            $trans_data[$key]['Transaction']['checker_id'] = $checker_id;
            $trans_data[$key]['Transaction']['maker_time_stamp'] = $maker_time_stamp;
            $trans_data[$key]['Transaction']['batch_no'] = $batch_no;
        }
        //$trans_data = array();
        //$reversed_arr = array_reverse($trans_data,true);
        //debug( $reversed_arr[count($trans_data)-1]['Transaction']['id']);
        $dowload = false;
        if ($trans_data) {
            $this->autoLayout = false;

            $res = $this->_convertToExcel($trans_data);
            $objPHPExcel = $res['excel_obj'];
            $filename = $res['filename'];
            $dowload = true;
            $reversed_arr = $trans_data;
            $reversed_arr = array_reverse($reversed_arr, true);
            $first_trans_id = $trans_data[0]['Transaction']['id'];
            $last_trans_id = $reversed_arr[count($trans_data) - 1]['Transaction']['id'];
            $auth_user = $this->Session->read('loginData');
            //save last transaction id
            $this->BankReportTracking->save(array(
                'id' => 1,
                'last_trans_id' => $last_trans_id
            ));
            //log the fisrt and last trans id current one for future reuse
            $this->BankReportLog->save(array(
                'bank_user_id' => $auth_user['id'],
                'start_trans_id' => $first_trans_id,
                'end_trans_id' => $last_trans_id,
                'maker_id' => $maker_id,
                'user_id' => $maker_id,
                'maker_time_stamp' => $maker_time_stamp,
                'checker_id' => $checker_id,
                'batch_no' => $batch_no
            ));
        } else {
            //$this->layout = 'bank_report_layout';
        }
        $this->set(compact('objPHPExcel', 'dowload', 'filename'));
    }

    public function report_history()
    {
        //$this->layout = 'bank_report_layout';
        $auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] == 'Supervisor' || $auth_user['user_level'] == 'Control User') { //For Supervisor and Control User
        } else {
            $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
        }

        $this->paginate = array(
            'BankReportLog' => array(
                //'conditions'=>array('BankReportLog.bank_user_id'=>$user_id),
                'contain' => array('BankUser'),
                'order' => array('BankReportLog.id' => 'desc'),
                'limit' => 10
            )
        );
        $transactData = $this->paginate('BankReportLog');
        //debug($transactData);
        $this->set('title_for_layout', 'Reports History');
        $this->set(compact('page_title', 'transactData'));
    }

    public function get_daily_history_report($start = null, $end = null, $log_id = null)
    {
        //$this->autoRender = false;
        $trans_data = $this->__get_report_data('history', $start, $end);
        $log_data = $this->BankReportLog->find('first', array(
            'conditions' => array('id' => $log_id),
            'recursive' => -1
        ));
        foreach ($trans_data as $key => $value) {
            $trans_data[$key]['Transaction']['maker_id'] = $log_data['BankReportLog']['maker_id'];
            $trans_data[$key]['Transaction']['user_id'] = $log_data['BankReportLog']['maker_id'];
            $trans_data[$key]['Transaction']['checker_id'] = $log_data['BankReportLog']['checker_id'];
            $trans_data[$key]['Transaction']['maker_time_stamp'] = $log_data['BankReportLog']['maker_time_stamp'];
            $trans_data[$key]['Transaction']['batch_no'] = $log_data['BankReportLog']['batch_no'];
        }
        //$trans_data = array();
        //$reversed_arr = array_reverse($trans_data,true);
        //debug( $reversed_arr[count($trans_data)-1]['Transaction']['id']);

        //debug($trans_data);
        // exit;
        $dowload = false;
        if ($trans_data) {
            $this->autoLayout = false;
            $res = $this->_convertToExcel($trans_data);
            $objPHPExcel = $res['excel_obj'];
            $filename = $res['filename'];
            $dowload = true;
        } else {
            //$this->layout = 'bank_report_layout';
        }
        $this->set(compact('objPHPExcel', 'dowload', 'filename'));
    }


    public function __get_report_data($type, $start, $end = null)
    {
        $conditions = array();
        $limit = 2000; //Dowload and process 2000 records per download
        if ($type == 'new') {
            $conditions = array('Transaction.id > ' => $start, 'Transaction.trans_type' => array('fee_payment','service_charge'));
        } elseif ($type == 'history') {
            $conditions = array('Transaction.id >= ' => $start, 'Transaction.id <= ' => $end, 'Transaction.trans_type' => array('fee_payment','service_charge'));
        }
        $find_params = array(
            'fields' => array('Transaction.id', 'Transaction.student_user_id', 'Transaction.created'),
            'conditions' => $conditions,
            'contain' => array(
                'TransactionItem' => array(
                    'fields' => array('TransactionItem.id', 'TransactionItem.credit_amt', 'TransactionItem.trans_currency', 'TransactionItem.fee_id','TransactionItem.actual_trans_type'),
                    'Fee' => array(
                        'fields' => array('Fee.id', 'Fee.fee_type_id')
                    )
                ),
                'StudentUser' => array('fields' => array('StudentUser.id', 'StudentUser.fname', 'StudentUser.mname', 'StudentUser.lname', 'StudentUser.index_number'))
            ),
            'order' => array('Transaction.id' => 'ASC'),
            'recursive' => 2
        );
        if ($type == 'new') {
            $find_params['limit'] = $limit;
        }


        $trans_data1 = $this->Transaction->find('all', $find_params);
        //debug($trans_data1);
        // debug($find_params);

        //$log = $this->Transaction->getDataSource()->getLog(false, false);
        //debug($log);

        $trans_data = array();
        foreach ($trans_data1 as $key => $value) {
            $count = count($value['TransactionItem']);
            if ($count == 1) {
                if ($value['TransactionItem'][0]['actual_trans_type'] == 'fee_payment' && empty($value['TransactionItem'][0]['Fee'])) {
                    //debug($value);
                } else {
                    $trans_data[$key] = $value;
                }
            } else {
                foreach ($value['TransactionItem'] as $key2 => $item) {
                    if ($item['actual_trans_type'] == 'fee_payment' && empty($item['Fee'])) {

                    } else {
                        $trans_data[$key]['Transaction'] = $value['Transaction'];
                        $trans_data[$key]['StudentUser'] = $value['StudentUser'];
                        $trans_data[$key]['TransactionItem'][$key2] = $item;
                    }
                }
            }
        }

        //VERY IMPORTANT QUERY HAS TO FILTER ON THE CURRENT BANK OF THE USER BRANCH CODE
        //Consider tying using branch from current login user and using that to filter on data rather than tying branch id to FeeType, what if in the future many branch what to use the sam Feetypes ?
        $auth_user = $this->Session->read('loginData');
        $usr_dt = $this->BankUser->getUserBranch($auth_user['id']);
        $user_branch_code = $usr_dt['BankBranch']['branch_code'];
        /*$retrun = array();*/

        foreach ($trans_data as $key => $value) {
            foreach ($value['TransactionItem'] as $key2 => $item) {

                if($item['actual_trans_type'] == 'service_charge'){
                    $feetype_id = '23'; //Reserved id for service charge
                }
                elseif($item['actual_trans_type'] == 'service_charge_upsa'){
                    $feetype_id = '25'; //Reserved id for service charge upsa
                }
                else{
                    $feetype_id = $item['Fee']['fee_type_id'];
                }

                $feetype_data = $this->FeeType->find('first', array(
                    'fields' => array('FeeType.id', 'FeeType.fee_category', 'FeeType.description', 'FeeType.account_no', 'FeeType.bank_branch_id'),
                    'conditions' => array('FeeType.id' => $feetype_id),
                    'contain' => array('BankBranch' => array('fields' => array('BankBranch.id', 'BankBranch.branch_name', 'BankBranch.branch_code'))),
                    'recursive' => 1
                ));
                //if($user_branch_code == $feetype_data['BankBranch']['branch_code']){
                //$trans_data[$key] = $value;
                //$trans_data[$key]['Transaction']['branch_code']=$feetype_data['BankBranch']['branch_code'];
                $trans_data[$key]['Transaction']['branch_code'] = $user_branch_code;
                $trans_data[$key]['TransactionItem'][$key2]['FeeType'] = $feetype_data['FeeType'];
                $trans_data[$key]['TransactionItem'][$key2]['BankBranch'] = $feetype_data['BankBranch'];
                // }
            }
        }

        return $trans_data;
    }


    function transactions()
    {
        $auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] == 'Supervisor' || $auth_user['user_level'] == 'Control User') { //For Supervisor and Control User
        } else {
            $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
        }

        $start_dt = $default_start_dt = date('Y-m-01');
        $end_dt = $default_end_dt = date('Y-m-t');
        $branch_id = $auth_user['bank_branch_id'];
        $teller = null;
        $trans_ref_no  = null;
        if ($this->request->is('post')) {
            $p_data = $this->request['data']['Query'];
            $p_data = $this->BankReportLog->sanitize($p_data);
            $start_dt = $default_start_dt = $p_data['start_dt'];
            $end_dt = $default_end_dt = $p_data['end_dt'];
            $branch_id = $p_data['branch'];
            $trans_ref_no  = $p_data['trans_ref_no'];
            $teller = $p_data['teller'];
        }
        if (count($trans_ref_no) == 0) {
            $trans_ref_no = null;
        }
        if ($teller == 0) {
            $teller = null;
        }
        if ($branch_id == 0) {
            $branch_id = null;
        }
        $type = "'credit_account','debit_account'";

        $data_table = $this->getTransactions($start_dt, $end_dt,$type, $branch_id, $teller,$trans_ref_no);

        if ($auth_user['user_level'] == 'Supervisor') { //For Supervisor
            $data = $this->BankBranch->getBranchAndTellers($auth_user['bank_branch_id']);
            $branch_list = $data['branch_list'];
            $branch_tellers = $data['branch_tellers'];
        }
        if ($auth_user['user_level'] == 'Control User') { //Control User
            $data = $this->BankBranch->getBranchAndTellers();
            $branch_list = array_merge(array('0' => 'All Branches'), $data['branch_list']);
            $branch_tellers = $data['branch_tellers'];
        }
        $controller = $this;
        if ($teller == null) {
            $teller = 0;
        }
        if ($branch_id == null) {
            $branch_id = 0;
        }
        //debug($teller);

        $this->set(compact('page_title', 'branch_list', 'default_start_dt', 'default_end_dt', 'data_table', 'controller', 'branch_tellers', 'branch_id', 'teller'));
    }


    function print_export_transactions()
    {
        $auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] == 'Supervisor' || $auth_user['user_level'] == 'Control User') { //For Supervisor and Control User
        } else {
            $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
        }

        $download = false;

        $_POST = $this->BankReportLog->sanitize($_POST);

        $media_type = $_POST['data_type'];
        $start_dt = $_POST['data_start_dt'];
        $end_dt = $_POST['data_end_dt'];
        $branch_id = $_POST['data_branch'];
        $teller = $_POST['data_teller'];
        $trans_ref_no  = $_POST['data_trans_ref_no'];
        if (count($trans_ref_no) == 0) {
            $trans_ref_no = null;
        }
        if ($teller == 0) {
            $teller = null;
        }
        if ($branch_id == 0) {
            $branch_id = null;
        }
        $type = "'credit_account','debit_account'";
        $data_table = $this->getTransactions($start_dt, $end_dt,$type, $branch_id, $teller,$trans_ref_no);

        $this->autoLayout = false;

        if ($media_type == 'export') {
            if (!empty($data_table)) {
                $download = true;
            }
            $tl_credit = 0;
            $tl_debit = 0;
            $t_body_data = array();
            foreach ($data_table as $obj) {
                $trans = $obj['Transaction'];
                $teller_d = $obj['BankUser'];
                $branch_d = $obj['BankBranch'];
                $trans_details = $obj['TransactionBankDetail'];
                $trans_item = $obj['TransactionItem'];

                $rows = array();

                $credit = doubleval($trans_item['credit_amt']);
                $debit = doubleval($trans_item['debit_amt']);

                $tl_credit = $tl_credit + $credit;
                $tl_debit = $tl_debit + $debit;

                $rows[] = $trans['created'];
                $rows[] = $trans['trans_ref_no'];
                $rows[] = $branch_d['branch_name'];
                $rows[] = $teller_d['fname'] . ' ' . $teller_d['mname'] . ' ' . $teller_d['lname'];
                $rows[] = $this->formatNumber($credit, 'number');
                //$rows[]= $this->formatNumber($debit,'number');
                $t_body_data[] = $rows;

            }

            $rows = array();
            $rows[] = '';
            $rows[] = '';
            $rows[] = '';
            $rows[] = 'Total';
            $rows[] = $this->formatNumber($tl_credit, 'number');
            //$rows[]= $this->formatNumber($tl_debit,'number');

            $t_body_data[] = $rows;

            $list_data = $t_body_data;
            $list_headers = array('Date', 'Reference', 'Branch', 'Teller', 'Credit GHc');
            $filename = 'Transactions Trails';
            $res = $this->convertToExcel($list_headers, $list_data, $filename);
            $objPHPExcel = $res['excel_obj'];
            $filename = $res['filename'];
        } elseif ($media_type == 'print') {
            $this->autoLayout = true;
            $this->layout = 'print_layout';
        }
        //$print_title = $table_title;
        $controller = $this;

        $this->set(compact('controller', 'download', 'print_title', 'table_title', 'data_table', 'fee_type', 'objPHPExcel', 'download', 'filename', 'media_type'));
    }


    function print_export_revert_transactions()
    {
        $auth_user = $this->Session->read('loginData');
        if ($auth_user['user_level'] == 'Supervisor' || $auth_user['user_level'] == 'Control User') { //For Supervisor and Control User
        } else {
            $this->redirect(array('controller' => 'Bank', 'action' => 'index'));
        }

        $download = false;

        $_POST = $this->BankReportLog->sanitize($_POST);

        $media_type = $_POST['data_type'];
        $start_dt = $_POST['data_start_dt'];
        $end_dt = $_POST['data_end_dt'];
        $branch_id = $_POST['data_branch'];
        $teller = $_POST['data_teller'];
        if ($teller == 0) {
            $teller = null;
        }
        if ($branch_id == 0) {
            $branch_id = null;
        }
        $type = "'credit_reversal'";
        $data_table = $this->getTransactions($start_dt, $end_dt,$type, $branch_id, $teller);

        $this->autoLayout = false;

        if ($media_type == 'export') {
            if (!empty($data_table)) {
                $download = true;
            }
            $tl_credit = 0;
            $tl_debit = 0;
            $t_body_data = array();
            foreach ($data_table as $obj) {
                $trans = $obj['Transaction'];
                $reason = $obj['Transaction']['revert_reason'];
                $teller_d = $obj['BankUser'];
                $branch_d = $obj['BankBranch'];
                $trans_details = $obj['TransactionBankDetail'];
                $trans_item = $obj['TransactionItem'];

                $rows = array();

                $credit = doubleval($trans_item['credit_amt']);
                $debit = doubleval($trans_item['debit_amt']);

                $tl_credit = $tl_credit + $credit;
                $tl_debit = $tl_debit + $debit;

                $rows[] = $trans['created'];
                $rows[] = $trans['trans_ref_no'];
                $rows[] = $branch_d['branch_name'];
                $rows[] = $teller_d['fname'] . ' ' . $teller_d['mname'] . ' ' . $teller_d['lname'];
                $rows[] = $this->formatNumber($credit, 'number');
                $rows[]= $reason;
                $t_body_data[] = $rows;

            }

            $rows = array();
            $rows[] = '';
            $rows[] = '';
            $rows[] = '';
            $rows[] = 'Total';
            $rows[] = $this->formatNumber($tl_credit, 'number');
            $rows[] = '';

            $t_body_data[] = $rows;

            $list_data = $t_body_data;
            $list_headers = array('Date', 'Reference', 'Branch', 'Teller', 'Debit GHc','Reason');
            $filename = 'Revert Transactions Trails';
            $res = $this->convertToExcel($list_headers, $list_data, $filename);
            $objPHPExcel = $res['excel_obj'];
            $filename = $res['filename'];
        } elseif ($media_type == 'print') {
            $this->autoLayout = true;
            $this->layout = 'print_layout';
        }
        //$print_title = $table_title;
        $controller = $this;

        $this->set(compact('controller', 'download', 'print_title', 'table_title', 'data_table', 'fee_type', 'objPHPExcel', 'download', 'filename', 'media_type'));
    }


    /* private function getTransactions($start_dt,$end_dt,$branch = null,$teller=null){
         $condition_array = array('Transaction.trans_type'=>array('credit_account','debit_account'));

         if($branch != null){
             $condition_array['BankUser.bank_branch_id'] = $branch;
         }
         if($teller != null){
             $condition_array['Transaction.bank_user_id'] = $teller;
         }
         if(!empty($start_dt) || !empty($end_dt)){
             if(!empty($start_dt) && empty($end_dt)){
                 $condition_array['DATE(Transaction.created) >=']=$start_dt;
                 $condition_array['DATE(Transaction.created) <=']=$start_dt;
             }
             elseif(empty($start_dt) && !empty($end_dt)){
                 $condition_array['DATE(Transaction.created) >=']=date('Y-m-d');//from now
                 $condition_array['DATE(Transaction.created) <=']=$end_dt; //to a given end date
             }
             elseif(!empty($start_dt) && !empty($end_dt)){
                 $condition_array['DATE(Transaction.created) >=']=$start_dt;//from now
                 $condition_array['DATE(Transaction.created) <=']=$end_dt; //to a given end date
             }
         }

         $contain=array(
             'TransactionItem'=>array(
                 'fields'=>array('TransactionItem.id','TransactionItem.desc','TransactionItem.credit_amt','TransactionItem.debit_amt','TransactionItem.transaction_id')
             ),
             'BankUser'=>array(
                 'fields'=>array('BankUser.id','BankUser.username','BankUser.fname','BankUser.mname','BankUser.lname'),
                 'BankBranch'=>array(
                     'fields'=>array('BankBranch.id','BankBranch.branch_name','BankBranch.branch_name_full')
                 )
             ),
             'TransactionBankDetail'=>array(
                 'fields'=>array('TransactionBankDetail.id','TransactionBankDetail.payee_name','TransactionBankDetail.payee_phone','TransactionBankDetail.payee_address','TransactionBankDetail.payment_method')
             ),
         );
         $fields=array('Transaction.id','Transaction.created','Transaction.trans_type','Transaction.bank_user_id','Transaction.trans_ref_no','Transaction.reverted','Transaction.revert_reason','Transaction.revert_dt','Transaction.reverted_by');
         $data_table = $this->Transaction->find('all', array(
             'fields'=>$fields,
             'conditions' => $condition_array,
             'contain'=>$contain,
             'order' => array("Transaction.id"=>'asc'),
             //'recursive' => 3
         ));

         //debug($data_table);
         return $data_table;
     }*/


    private function getTransactions($start_dt, $end_dt, $type,$branch = null, $teller = null,$reference=null)
    {
        $conditions_str = " WHERE Transaction.trans_type IN ($type)";
        if($reference !=null){
            $conditions_str .= " AND Transaction.trans_ref_no='" . $reference . "'";
        }
        else{
            if ($branch != null) {
                $conditions_str .= " AND BankUser.bank_branch_id='" . $branch . "'";
            }
            if ($teller != null) {
                $conditions_str .= " AND Transaction.bank_user_id='" . $teller . "'";
            }

            if (!empty($start_dt) || !empty($end_dt)) {
                if (!empty($start_dt) && empty($end_dt)) {
                    $conditions_str .= " AND DATE(Transaction.created) >='" . $start_dt . "' AND DATE(Transaction.created) <='" . $start_dt . "'";
                } elseif (empty($start_dt) && !empty($end_dt)) {
                    $conditions_str .= " AND DATE(Transaction.created) >='" . date('Y-m-d') . "' AND DATE(Transaction.created) <='" . $end_dt . "'";
                } elseif (!empty($start_dt) && !empty($end_dt)) {
                    $conditions_str .= " AND DATE(Transaction.created) >='" . $start_dt . "' AND DATE(Transaction.created) <='" . $end_dt . "'";
                }
            }
        }

        $joins = " LEFT JOIN transaction_bank_details AS TransactionBankDetail ON Transaction.id=TransactionBankDetail.transaction_id";
        $joins .= " LEFT JOIN transaction_items AS TransactionItem ON Transaction.id=TransactionItem.transaction_id";
        $joins .= " LEFT JOIN bank_users AS BankUser ON Transaction.bank_user_id=BankUser.id";
        $joins .= " LEFT JOIN bank_branches AS BankBranch ON BankUser.bank_branch_id=BankBranch.id";
        $joins .= " LEFT JOIN student_users AS StudentUser ON Transaction.student_user_id=StudentUser.id";

        $fields_str = " Transaction.id,Transaction.created,Transaction.trans_type,Transaction.bank_user_id,Transaction.trans_ref_no,Transaction.reverted,Transaction.revert_reason,Transaction.revert_dt,Transaction.reverted_by,";
        $fields_str .= " TransactionItem.id,TransactionItem.desc,TransactionItem.credit_amt,TransactionItem.debit_amt,TransactionItem.transaction_id,";
        $fields_str .= " TransactionBankDetail.id,TransactionBankDetail.payee_name,TransactionBankDetail.payee_phone,TransactionBankDetail.payee_address,TransactionBankDetail.payment_method,";
        $fields_str .= " BankUser.id,BankUser.username,BankUser.fname,BankUser.mname,BankUser.lname,";
        $fields_str .= " BankBranch.id,BankBranch.branch_name,BankBranch.branch_name_full,";
        $fields_str .= " StudentUser.id,StudentUser.index_number,StudentUser.fname,StudentUser.mname,StudentUser.lname,StudentUser.account_balance";

        $query = "SELECT $fields_str FROM transactions AS Transaction $joins $conditions_str ORDER BY Transaction.id DESC;";
        //debug($query);

        $data_table = $this->Transaction->query($query);

        // debug($data_table);
        //exit;
        return $data_table;
    }


    private function _convertToExcel($param)
    {
        ini_set("memory_limit", "512M");
        set_time_limit('1200');

        $count = 0;
        $total_amount = 0.00;
        $maker_id = $param[0]['Transaction']['maker_id'];
        $user_id = $param[0]['Transaction']['user_id'];
        $checker_id = $param[0]['Transaction']['checker_id'];
        $maker_time_stamp = $param[0]['Transaction']['maker_time_stamp'];
        $batch_no = 'up' . $param[0]['Transaction']['batch_no'];
        $batch_code_master = $param[0]['Transaction']['branch_code'];
        $filename = "UPSATXN" . date("Ymd", strtotime($maker_time_stamp));
        //$debit_account = '25008990';//Withholding Account
        $debit_account = '0241811429351'; //Withholding Account
        $first_row_constant_master = 'fcclive.DETB_UPLOAD_MASTER';
        $first_row_constant_detail = 'fcclive.DETB_UPLOAD_DETAIL';

        foreach ($param as $val) {
            $count = $count + count($val['TransactionItem']);
            foreach ($val['TransactionItem'] as $item) {
                $total_amount = $total_amount + doubleval($item['credit_amt']);
            }
        }
        $total_entries = $count * 2;

        $cacheMethod = PHPExcel_CachedObjectStorageFactory:: cache_to_phpTemp;
        $cacheSettings = array(' memoryCacheSize ' => '8MB');
        PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);

        $objPHPExcel = new PHPExcel();

        // Set document properties
        $objPHPExcel->getProperties()->setCreator("Interpay")
            ->setLastModifiedBy("Interpay")
            ->setTitle("Office 2007 XLSX Interpay Document")
            ->setSubject("Office 2007 XLSX Interpay Document")
            ->setDescription("Interpay document for Office 2007 XLSX, generated using PHP classes.")
            ->setKeywords("office 2007 openxml php")
            ->setCategory("Interpay result file");

        // Set default font
        $objPHPExcel->getDefaultStyle()->getFont()->setName('Arial')->setSize(10);
        $objPHPExcel->getActiveSheet()->setCellValue('A1', $first_row_constant_master);
        $objPHPExcel->getActiveSheet()->getStyle('A2:AD2')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->setCellValue('B1', '~~END~~')
            ->setCellValue('A2', 'BRANCH_CODE')
            ->setCellValue('B2', 'SOURCE_CODE')
            ->setCellValue('C2', 'BATCH_NO')
            ->setCellValue('D2', 'TOTAL_ENTRIES')
            ->setCellValue('E2', 'UPLOADED_ENTRIES')
            ->setCellValue('F2', 'BALANCING')
            ->setCellValue('G2', 'BATCH_DESC')
            ->setCellValue('H2', 'MIS_REQUIRED')
            ->setCellValue('I2', 'AUTO_AUTH')
            ->setCellValue('J2', 'GL_OFFSET_ENTRY_REQD')
            ->setCellValue('K2', 'UDF_UPLOAD_REQD')
            ->setCellValue('L2', 'OFFSET_GL')
            ->setCellValue('M2', 'TXN_CODE')
            ->setCellValue('N2', 'DR_ENT_TOTAL')
            ->setCellValue('O2', 'CR_ENT_TOTAL')
            ->setCellValue('P2', 'USER_ID')
            ->setCellValue('Q2', 'UPLOAD_STAT')
            ->setCellValue('R2', 'JOBNO')
            ->setCellValue('S2', 'SYSTEM_BATCH')
            ->setCellValue('T2', 'POSITION_REQD')
            ->setCellValue('U2', 'MAKER_ID')
            ->setCellValue('V2', 'MAKER_DT_STAMP')
            ->setCellValue('W2', 'CHECKER_ID')
            ->setCellValue('X2', 'CHECKER_DT_STAMP')
            ->setCellValue('Y2', 'MOD_NO')
            ->setCellValue('Z2', 'AUTH_STAT')
            ->setCellValue('AA2', 'RECORD_STAT')
            ->setCellValue('AB2', 'ONCE_AUTH')
            ->setCellValue('AC2', 'UPLOAD_DATE')
            ->setCellValue('AD2', 'UPLOAD_FILE_NAME')
            ->setCellValue('AE2', '');

        //writing main data for sheet one
        $objPHPExcel->getActiveSheet()->setCellValueExplicit('A3', $batch_code_master, PHPExcel_Cell_DataType::TYPE_STRING)
            ->setCellValue('B3', $filename) /* sourcecode = should be same as filename, unique*/
            ->setCellValueExplicit('C3', $batch_no, PHPExcel_Cell_DataType::TYPE_STRING) /* batchcode_should be an auto incrementig serial*/
            ->setCellValue('D3', $total_entries) /* total entries = should be total number of transactions */
            ->setCellValue('E3', '0')
            ->setCellValue('F3', 'N')
            ->setCellValue('G3', $filename) /* batch description-- should be the same as filename*/
            ->setCellValue('H3', 'N')
            ->setCellValue('I3', 'N')
            ->setCellValue('J3', 'N')
            ->setCellValue('K3', 'N')
            ->setCellValue('L3', '')
            ->setCellValue('M3', '534')
            ->setCellValue('N3', $total_amount) /* should be total amount debited */
            ->setCellValue('O3', $total_amount) /* should be total amount credited*/
            ->setCellValue('P3', $user_id)
            ->setCellValue('Q3', 'Y')
            ->setCellValue('R3', '1')
            ->setCellValue('S3', 'N')
            ->setCellValue('T3', 'N')
            ->setCellValue('U3', $maker_id) /* MAKER ID*/
            ->setCellValue('V3', date("d-M-Y", strtotime($maker_time_stamp))) /*date file was created format (12-Jun-2012) */
            ->setCellValue('W3', $checker_id) /* CHECKER ID*/
            ->setCellValue('X3', '')
            ->setCellValue('Y3', '1')
            ->setCellValue('Z3', 'U')
            ->setCellValue('AA3', 'O')
            ->setCellValue('AB3', 'Y')
            ->setCellValue('AC3', '')
            ->setCellValue('AD3', '')
            ->setCellValue('AE3', '~~END~~');

        // change styling for a group of cells
        $objPHPExcel->getActiveSheet()->getStyle('A4:AE4')->getFont()->setBold(true);


        $objPHPExcel->getActiveSheet()->setCellValue('A4', '~~END~~')
            ->setCellValue('B4', '~~END~~')
            ->setCellValue('C4', '~~END~~')
            ->setCellValue('D4', '~~END~~')
            ->setCellValue('E4', '~~END~~')
            ->setCellValue('F4', '~~END~~')
            ->setCellValue('G4', '~~END~~')
            ->setCellValue('H4', '~~END~~')
            ->setCellValue('I4', '~~END~~')
            ->setCellValue('J4', '~~END~~')
            ->setCellValue('K4', '~~END~~')
            ->setCellValue('L4', '~~END~~')
            ->setCellValue('M4', '~~END~~')
            ->setCellValue('N4', '~~END~~')
            ->setCellValue('O4', '~~END~~')
            ->setCellValue('P4', '~~END~~')
            ->setCellValue('Q4', '~~END~~')
            ->setCellValue('R4', '~~END~~')
            ->setCellValue('S4', '~~END~~')
            ->setCellValue('T4', '~~END~~')
            ->setCellValue('U4', '~~END~~')
            ->setCellValue('V4', '~~END~~')
            ->setCellValue('W4', '~~END~~')
            ->setCellValue('X4', '~~END~~')
            ->setCellValue('Y4', '~~END~~')
            ->setCellValue('Z4', '~~END~~')
            ->setCellValue('AA4', '~~END~~')
            ->setCellValue('AB4', '~~END~~')
            ->setCellValue('AC4', '~~END~~')
            ->setCellValue('AD4', '~~END~~')
            ->setCellValue('AE4', '~~END~~');
        // Rename worksheet
        $objPHPExcel->getActiveSheet()->setTitle('Upload_Master');

        /** Lock this sheet */
        $objPHPExcel->getActiveSheet()->getProtection()->setPassword('los enuve aspectos');
        $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);

        // create another sheet for upload Details
        $objPHPExcel->createSheet();
        $objPHPExcel->setActiveSheetIndex(1);
        $objPHPExcel->getActiveSheet()->setCellValue('A1', $first_row_constant_detail);

        $objPHPExcel->getActiveSheet()->getStyle('A2:BI2')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->setCellValue('B1', '~~END~~')
            ->setCellValue('A2', 'INSTRUMENT_NO')
            ->setCellValue('B2', 'FIN_CYCLE')
            ->setCellValue('C2', 'PERIOD_CODE')
            ->setCellValue('D2', 'MIS_CODE')
            ->setCellValue('E2', 'REL_CUST')
            ->setCellValue('F2', 'ADDL_TEXT')
            ->setCellValue('G2', 'MIS_GROUP')
            ->setCellValue('H2', 'DW_AC_NO')
            ->setCellValue('I2', 'ACCOUNT_NEW')
            ->setCellValue('J2', 'TXN_MIS_1')
            ->setCellValue('K2', 'TXN_MIS_2')
            ->setCellValue('L2', 'TXN_MIS_3')
            ->setCellValue('M2', 'TXN_MIS_4')
            ->setCellValue('N2', 'TXN_MIS_5')
            ->setCellValue('O2', 'TXN_MIS_6')
            ->setCellValue('P2', 'TXN_MIS_7')
            ->setCellValue('Q2', 'TXN_MIS_8')
            ->setCellValue('R2', 'TXN_MIS_9')
            ->setCellValue('S2', 'TXN_MIS_10')
            ->setCellValue('T2', 'COMP_MIS_1')
            ->setCellValue('U2', 'COMP_MIS_2')
            ->setCellValue('V2', 'COMP_MIS_3')
            ->setCellValue('W2', 'COMP_MIS_4')
            ->setCellValue('X2', 'COMP_MIS_5')
            ->setCellValue('Y2', 'COMP_MIS_6')
            ->setCellValue('Z2', 'COMP_MIS_7')
            ->setCellValue('AA2', 'COMP_MIS_8')
            ->setCellValue('AB2', 'COMP_MIS_9')
            ->setCellValue('AC2', 'COMP_MIS_10')
            ->setCellValue('AD2', 'COST_CODE1')
            ->setCellValue('AE2', 'COST_CODE2')
            ->setCellValue('AF2', 'COST_CODE3')
            ->setCellValue('AG2', 'COST_CODE4')
            ->setCellValue('AH2', 'COST_CODE5')
            ->setCellValue('AI2', 'MIS_HEAD')
            ->setCellValue('AJ2', 'RELATED_ACCOUNT')
            ->setCellValue('AK2', 'RELATED_REF')
            ->setCellValue('AL2', 'POOL_CODE')
            ->setCellValue('AM2', 'REF_RATE')
            ->setCellValue('AN2', 'CALC_METHOD')
            ->setCellValue('AO2', 'BATCH_NO')
            ->setCellValue('AP2', 'MIS_FLAG')
            ->setCellValue('AQ2', 'MIS_GROUP_TXN')
            ->setCellValue('AR2', 'BRANCH_CODE')
            ->setCellValue('AS2', 'SOURCE_CODE')
            ->setCellValue('AT2', 'CURR_NO')
            ->setCellValue('AU2', 'UPLOAD_STAT')
            ->setCellValue('AV2', 'CCY_CD')
            ->setCellValue('AW2', 'INITIATION_DATE')
            ->setCellValue('AX2', 'AMOUNT')
            ->setCellValue('AY2', 'ACCOUNT')
            ->setCellValue('AZ2', 'ACCOUNT_BRANCH')
            ->setCellValue('BA2', 'TXN_CODE')
            ->setCellValue('BB2', 'DR_CR')
            ->setCellValue('BC2', 'LCY_EQUIVALENT')
            ->setCellValue('BD2', 'EXCH_RATE')
            ->setCellValue('BE2', 'VALUE_DATE')
            ->setCellValue('BF2', 'EXTERNAL_REF_NO')
            ->setCellValue('BG2', 'RESERVED_FUNDS_REF')
            ->setCellValue('BH2', 'DELETE_STAT')
            ->setCellValue('BI2', 'TXT_FILE_NAME')
            ->setCellValue('BJ2', '');

        //insert the data
        $count2 = 0;
        $curent_no = 1;
        $it = 3;
        foreach ($param as $val) {
            $stud_fullname = $val['StudentUser']['fname'] . ' ' . $val['StudentUser']['mname'] . ' ' . $val['StudentUser']['lname'];
            $index_number = $val['StudentUser']['index_number'];
            $initiation_date = date("d-M-Y", strtotime($val['Transaction']['created']));
            $count2 = $count2 + count($val['TransactionItem']);
            foreach ($val['TransactionItem'] as $item) {
                $amt = $item['credit_amt'];
                $trans_currency = $item['trans_currency'];
                $feetype_decs = strtoupper($item['FeeType']['description'] . ' BY ' . $index_number . '-' . $stud_fullname);
                $credit_acct = $item['FeeType']['account_no'];
                $bank_branch = strtoupper($item['BankBranch']['branch_name']);
                $branch_code = strtoupper($item['BankBranch']['branch_code']);

                //Credit Transaction
                $objPHPExcel->getActiveSheet()->setCellValue('B' . $it, 'FY' . date("Y", strtotime($maker_time_stamp)))
                    ->setCellValue('C' . $it, 'P' . date("m", strtotime($maker_time_stamp)))
                    ->setCellValue('F' . $it, $feetype_decs)//feetype description 'By' indexnumber-student full name
                    ->setCellValueExplicit('AO' . $it, $batch_no, PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValueExplicit('AR' . $it, $branch_code, PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValue('AS' . $it, $filename)
                    ->setCellValue('AT' . $it, $curent_no++)
                    ->setCellValue('AV' . $it, $trans_currency)
                    ->setCellValue('AW' . $it, $initiation_date) //Initaition Date
                    ->setCellValue('AX' . $it, $amt)
                    //->setCellValue('AY'.$it, )
                    ->setCellValueExplicit('AY' . $it, $credit_acct, PHPExcel_Cell_DataType::TYPE_STRING)//credit Acount number
                    //->setCellValue('AZ'.$it, '024')
                    ->setCellValueExplicit('AZ' . $it, '024', PHPExcel_Cell_DataType::TYPE_STRING)// Account branch ,BRANCH CODE the account is being held 024
                    ->setCellValue('BA' . $it, '230')
                    ->setCellValue('BB' . $it, 'C')//CREDIT
                    ->setCellValue('BC' . $it, $amt)
                    ->setCellValue('BE' . $it, date("d-M-Y", strtotime($maker_time_stamp))) //Value Date
                    ->setCellValue('BJ' . $it, '~~END~~');
                $it++;
                //Debit Transaction
                $objPHPExcel->getActiveSheet()->setCellValue('B' . $it, 'FY' . date("Y", strtotime($maker_time_stamp)))
                    ->setCellValue('C' . $it, 'P' . date("m", strtotime($maker_time_stamp)))
                    ->setCellValue('F' . $it, $feetype_decs)
                    ->setCellValueExplicit('AO' . $it, $batch_no, PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValueExplicit('AR' . $it, $branch_code, PHPExcel_Cell_DataType::TYPE_STRING)
                    ->setCellValue('AS' . $it, $filename)
                    ->setCellValue('AT' . $it, $curent_no++)
                    ->setCellValue('AV' . $it, $trans_currency)
                    ->setCellValue('AW' . $it, $initiation_date) //Initaition Date
                    ->setCellValue('AX' . $it, $amt)
                    // ->setCellValue('AY'.$it, $debit_account) //debit Acount number
                    ->setCellValueExplicit('AY' . $it, $debit_account, PHPExcel_Cell_DataType::TYPE_STRING)//credit Acount number
                    ->setCellValueExplicit('AZ' . $it, '024', PHPExcel_Cell_DataType::TYPE_STRING)// Account branch ,BRANCH CODE the account is being held 024
                    ->setCellValue('BA' . $it, '230')
                    ->setCellValue('BB' . $it, 'D')//CREDIT
                    ->setCellValue('BC' . $it, $amt)
                    ->setCellValue('BE' . $it, date("d-M-Y", strtotime($maker_time_stamp))) //Value Date
                    ->setCellValue('BJ' . $it, '~~END~~');

                $it++;
            }
        }

        $objPHPExcel->getActiveSheet()->setCellValue('A' . $it, '~~END~~')
            ->setCellValue('B' . $it, '~~END~~')
            ->setCellValue('C' . $it, '~~END~~')
            ->setCellValue('D' . $it, '~~END~~')
            ->setCellValue('E' . $it, '~~END~~')
            ->setCellValue('F' . $it, '~~END~~')
            ->setCellValue('G' . $it, '~~END~~')
            ->setCellValue('H' . $it, '~~END~~')
            ->setCellValue('I' . $it, '~~END~~')
            ->setCellValue('J' . $it, '~~END~~')
            ->setCellValue('K' . $it, '~~END~~')
            ->setCellValue('L' . $it, '~~END~~')
            ->setCellValue('M' . $it, '~~END~~')
            ->setCellValue('N' . $it, '~~END~~')
            ->setCellValue('O' . $it, '~~END~~')
            ->setCellValue('P' . $it, '~~END~~')
            ->setCellValue('Q' . $it, '~~END~~')
            ->setCellValue('R' . $it, '~~END~~')
            ->setCellValue('S' . $it, '~~END~~')
            ->setCellValue('T' . $it, '~~END~~')
            ->setCellValue('U' . $it, '~~END~~')
            ->setCellValue('V' . $it, '~~END~~')
            ->setCellValue('W' . $it, '~~END~~')
            ->setCellValue('X' . $it, '~~END~~')
            ->setCellValue('Y' . $it, '~~END~~')
            ->setCellValue('Z' . $it, '~~END~~')
            ->setCellValue('AA' . $it, '~~END~~')
            ->setCellValue('AB' . $it, '~~END~~')
            ->setCellValue('AC' . $it, '~~END~~')
            ->setCellValue('AD' . $it, '~~END~~')
            ->setCellValue('AE' . $it, '~~END~~')
            ->setCellValue('AF' . $it, '~~END~~')
            ->setCellValue('AG' . $it, '~~END~~')
            ->setCellValue('AH' . $it, '~~END~~')
            ->setCellValue('AI' . $it, '~~END~~')
            ->setCellValue('AJ' . $it, '~~END~~')
            ->setCellValue('AK' . $it, '~~END~~')
            ->setCellValue('AL' . $it, '~~END~~')
            ->setCellValue('AM' . $it, '~~END~~')
            ->setCellValue('AN' . $it, '~~END~~')
            ->setCellValue('AO' . $it, '~~END~~')
            ->setCellValue('AP' . $it, '~~END~~')
            ->setCellValue('AQ' . $it, '~~END~~')
            ->setCellValue('AR' . $it, '~~END~~')
            ->setCellValue('AS' . $it, '~~END~~')
            ->setCellValue('AT' . $it, '~~END~~')
            ->setCellValue('AU' . $it, '~~END~~')
            ->setCellValue('AV' . $it, '~~END~~')
            ->setCellValue('AW' . $it, '~~END~~')
            ->setCellValue('AX' . $it, '~~END~~')
            ->setCellValue('AY' . $it, '~~END~~')
            ->setCellValue('AZ' . $it, '~~END~~')
            ->setCellValue('BA' . $it, '~~END~~')
            ->setCellValue('BB' . $it, '~~END~~')
            ->setCellValue('BC' . $it, '~~END~~')
            ->setCellValue('BD' . $it, '~~END~~')
            ->setCellValue('BE' . $it, '~~END~~')
            ->setCellValue('BF' . $it, '~~END~~')
            ->setCellValue('BG' . $it, '~~END~~')
            ->setCellValue('BH' . $it, '~~END~~')
            ->setCellValue('BI' . $it, '~~END~~')
            ->setCellValue('BJ' . $it, '~~END~~');

        $objPHPExcel->getActiveSheet()->getStyle('A' . $it . ':BJ' . $it)->getFont()->setBold(true);

        // Rename worksheet 2 to upload Detail
        $objPHPExcel->getActiveSheet()->setTitle('Upload_Detail');

        /** Lock this sheet */
        $objPHPExcel->getActiveSheet()->getProtection()->setPassword('los enuve aspectos');
        $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setSort(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setInsertRows(true);
        $objPHPExcel->getActiveSheet()->getProtection()->setFormatCells(true);

        // Set active sheet index to the first sheet, so Excel opens this as the first sheet
        $objPHPExcel->setActiveSheetIndex(0);

        return array(
            'excel_obj' => $objPHPExcel,
            'filename' => $filename
        );
    }


    private function getNotification()
    {
        $auth_user = $this->Session->read('loginData');
        $user_id = $auth_user['id'];
        $notifications = $this->StudentNotification->find('all', array(
            'conditions' => array('StudentNotification.student_user_id' => $user_id, 'StudentNotification.deleted' => 'n'),
            'recursive' => -1
        ));
        return $notifications;
    }


    /***************************  BANK ADMIN SECTION *********************/

    function admin_users($type = 'get')
    {

        if ($this->request->is('ajax')) {
            $this->autoRender = false;
            $this->autoLayout = false;
            $auth_user = $this->Session->read('loginData');
            switch ($type) {
                case 'get' :
                    $_POST = $this->BankUser->sanitize($_POST);
                    /**  Get posted data */
                    $page = isset($_POST['page']) ? $_POST['page'] : 1;
                    /** The current page */
                    $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
                    /** Sort column */
                    $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
                    /** Sort order */
                    $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
                    /** Search column */
                    $search_query = isset($_POST['query']) ? $_POST['query'] : '';
                    /** Search string */
                    $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
                    $limit = $rp;
                    $start = ($page - 1) * $rp;

                    //get users id for this company only

                    $condition_array = array(
                        'BankUser.deleted' => 'n'
                    );

                    if (!empty($search_query)) {
                        if ($qtype == 'username') {
                            $condition_array = array(
                                'BankUser.username' => $search_query,
                                'BankUser.deleted' => 'n'
                            );
                        } else {
                            $condition_array = array(
                                "BankUser.$qtype LIKE" => $search_query . '%',
                                'BankUser.deleted' => 'n'
                            );
                        }
                    }
                    $contain = array('BankBranch');
                    $data_table = $this->BankUser->find('all', array('conditions' => $condition_array, 'contain' => $contain, 'order' => "BankUser.$sortname $sortorder", 'limit' => $start . ',' . $limit, 'recursive' => 1));
                    $data_table_count = $this->BankUser->find('count', array('conditions' => $condition_array, 'recursive' => -1));

                    $total_records = $data_table_count;

                    if ($data_table) {
                        $return_arr = array();
                        foreach ($data_table as $obj) {
                            $return_arr[] = array(
                                'id' => $obj['BankUser']['id'],
                                'cell' => array(
                                    $obj['BankUser']['id'],
                                    $obj['BankUser']['fname'],
                                    $obj['BankUser']['mname'],
                                    $obj['BankUser']['lname'],
                                    $obj['BankUser']['username'],
                                    $obj['BankUser']['user_level'],
                                    $obj['BankUser']['active'],
                                    $obj['BankBranch']['branch_name'],
                                ),
                                'property' => array(
                                    'bg_color' => $obj['BankUser']['bg_color']
                                )
                            );
                        }
                        return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
                    } else {
                        return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
                    }
                    break;

                case 'save' :
                    if ($_POST['id'] == 0) {
                        //check if username does not exist for in this company
                        $username = $_POST['username'];
                        $res = $this->_validateUsername($username);
                        if ($res) {
                            return json_encode(array('success' => 1, 'msg' => 'Username already exist.'));
                        }
                    }
                    $data = array('BankUser' => $_POST);
                    $data['BankUser']['user_type'] = 'bank_user';
                    $pass = '';
                    if ($_POST['id'] == 0) {
                        $pass = $this->randomPassString(8);
                        $data['BankUser']['password'] = md5($pass);
                    } else {
                        $bk_user = $this->BankUser->getUserById($_POST['id']);
                        if ($bk_user['BankUser']['active'] == 'Disabled' && $_POST['active'] == 'Enabled') {
                            $data['BankUser']['bg_color'] = '';
                            $modObj = ClassRegistry::init('BankFailedLogin');
                            $re = $modObj->deleteAll(array('BankFailedLogin.username' => $_POST['username']), false);
                        }
                    }

                    if ($_POST['id'] > 0) {
                        $data['BankUser']['modified_by'] = $auth_user['id'];
                    } else {
                        $data['BankUser']['created_by'] = $auth_user['id'];
                    }

                    if ($this->BankUser->save($data['BankUser'])) {
                        if ($_POST['id'] > 0) {
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved'));
                        } else { //If new pass back extra data if needed.
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved!  The default password is ' . $pass, 'id' => $this->BankUser->id));
                        }
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Some errors occurred.'));
                    }
                    break;

                case 'reset_password':
                    $data = array(
                        'id' => $_POST['id'],
                        'password' => md5(trim($_POST['password']))
                    );
                    if ($this->BankUser->save($data)) {
                        return json_encode(array('code' => 0, 'msg' => 'Password has been reset.'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Password could not be reset.'));
                    }

                    break;

                case 'delete':
                    $ids = $_POST['ids'];
                    $modObj = ClassRegistry::init('BankUser');
                    $result = $modObj->updateAll(
                        array('BankUser.deleted' => "'y'"),
                        array('BankUser.id' => $ids)
                    );
                    if ($result) {
                        echo json_encode(array('code' => 0, 'msg' => 'Data Deleted!'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Data cannot be deleted'));
                    }
                    break;
            }
        }

        $list = $this->BankBranch->find('all', array(
            'recursive' => -1
        ));
        $branch_list = array();
        foreach ($list as $value) {
            $branch_list[] = array('id' => $value['BankBranch']['id'], 'name' => $value['BankBranch']['branch_name']);
        }

        //$this->layout = 'bank_admin_layout';

        $this->set(compact('branch_list'));
    }


    function _validateUsername($username = null)
    {
        $data = $this->BankUser->find('first', array(
            'conditions' => array('BankUser.username' => $username),
            'recursive' => -1
        ));

        if ($data) {
            return true;
        } else {
            return false;
        }
    }


    function randomPassString($length)
    {
        return $this->randomString($length);
    }


    function admin_branches($type = 'get')
    {

        if ($this->request->is('ajax')) {
            $this->autoRender = false;
            $this->autoLayout = false;
            $auth_user = $this->Session->read('loginData');
            switch ($type) {
                case 'get' :
                    $_POST = $this->BankUser->sanitize($_POST);
                    /**  Get posted data */
                    $page = isset($_POST['page']) ? $_POST['page'] : 1;
                    /** The current page */
                    $sortname = isset($_POST['sortname']) ? $_POST['sortname'] : 'id';
                    /** Sort column */
                    $sortorder = isset($_POST['sortorder']) ? $_POST['sortorder'] : 'desc';
                    /** Sort order */
                    $qtype = isset($_POST['qtype']) ? $_POST['qtype'] : '';
                    /** Search column */
                    $search_query = isset($_POST['query']) ? $_POST['query'] : '';
                    /** Search string */
                    $rp = isset($_POST['rp']) ? $_POST['rp'] : 10;
                    $limit = $rp;
                    $start = ($page - 1) * $rp;

                    //get users id for this company only

                    $condition_array = array(
                        'BankBranch.deleted' => 'n'
                    );

                    if (!empty($search_query)) {
                        if ($qtype == 'branch_code') {
                            $condition_array = array(
                                'BankBranch.branch_code' => $search_query,
                                'BankBranch.deleted' => 'n'
                            );
                        } else {
                            $condition_array = array(
                                "BankBranch.$qtype LIKE" => $search_query . '%',
                                'BankBranch.deleted' => 'n'
                            );
                        }
                    }
                    $data_table = $this->BankBranch->find('all', array('conditions' => $condition_array, 'order' => "BankBranch.$sortname $sortorder", 'limit' => $start . ',' . $limit, 'recursive' => 1));
                    $data_table_count = $this->BankBranch->find('count', array('conditions' => $condition_array, 'recursive' => -1));

                    $total_records = $data_table_count;

                    if ($data_table) {
                        $return_arr = array();
                        foreach ($data_table as $obj) {
                            $return_arr[] = array(
                                'id' => $obj['BankBranch']['id'],
                                'cell' => array(
                                    $obj['BankBranch']['id'],
                                    $obj['BankBranch']['branch_name'],
                                    $obj['BankBranch']['branch_code'],
                                    $obj['BankBranch']['branch_name_full'],
                                    $obj['BankBranch']['address'],
                                    $obj['BankBranch']['phone1'],
                                    $obj['BankBranch']['phone2']
                                )
                            );
                        }
                        return json_encode(array('success' => true, 'total' => $total_records, 'page' => $page, 'rows' => $return_arr));
                    } else {
                        return json_encode(array('success' => false, 'total' => $total_records, 'page' => $page, 'rows' => array()));
                    }
                    break;

                case 'save' :
                    $data = array('BankBranch' => $_POST);
                    $pass = '';

                    if ($_POST['id'] > 0) {
                        $data['BankBranch']['modified_by'] = $auth_user['id'];
                    } else {
                        $data['BankBranch']['created_by'] = $auth_user['id'];
                    }

                    if ($this->BankBranch->save($data['BankBranch'])) {
                        if ($_POST['id'] > 0) {
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved'));
                        } else { //If new pass back extra data if needed.
                            return json_encode(array('code' => 0, 'msg' => 'Data Saved! ', 'id' => $this->BankBranch->id));
                        }
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Some errors occurred.'));
                    }
                    break;

                case 'delete':
                    $ids = $_POST['ids'];
                    $modObj = ClassRegistry::init('BankBranch');
                    $result = $modObj->updateAll(
                        array('BankBranch.deleted' => "'y'"),
                        array('BankBranch.id' => $ids)
                    );
                    if ($result) {
                        echo json_encode(array('code' => 0, 'msg' => 'Data Deleted!'));
                    } else {
                        echo json_encode(array('code' => 1, 'msg' => 'Data cannot be deleted'));
                    }
                    break;
            }
        }

        $list = $this->BankBranch->find('all', array(
            'recursive' => -1
        ));
        $branch_list = array();
        foreach ($list as $value) {
            $branch_list[] = array('id' => $value['BankBranch']['id'], 'name' => $value['BankBranch']['branch_name']);
        }

        //$this->layout = 'bank_admin_layout';

        $this->set(compact('branch_list'));
    }

    /************************** END BANK ADMIN SECTION *******************/

}